--EXEC SP_CREATE_UPDATE_BIN_INFO_FOR_ASSEMBLY_RELEASE 1812, 98, 'DP0197', 'DP0197', 1650, null, null, 'AdaptorLot'
ALTER PROCEDURE SP_CREATE_UPDATE_BIN_INFO_FOR_ASSEMBLY_RELEASE
(
	@DB_PROCESSSTEP_ID	INT,
	@DB_LOT_ID			INT,
	@LOT_NUMBER			VARCHAR(50),
	@FAB_LOT_NUMBER		VARCHAR (50),
	@QTY_IN				INT,
	@DIEBANK_WAFER_IDS  VARCHAR(200),
	@P_TYPE				VARCHAR(20),
	@LOT_FROM			VARCHAR(50)	
)

	AS 
	BEGIN
		DECLARE @SORT_PROCESSSTEP_ID	INT
		DECLARE @FAB_PROCESSSTEP_ID		INT
		DECLARE @FAB_LOT_ID				INT
		DECLARE @SORT_LOT_ID			INT
		DECLARE @WAFER_IDS_TABLE		TABLE ( WAFER_ID INT )
		DECLARE @RESULT_WAFER_IDS_TABLE	TABLE ( WAFER_ID INT )
		DECLARE @SHIPPED_WAFER_IDS		VARCHAR(200)
		DECLARE @SHIPPED_WAFER_ID		VARCHAR(200)
		DECLARE @ALL_SHIPPED_WAFER_IDS  VARCHAR(200)
		DECLARE @DEFAULT_WAFERS         VARCHAR(200)
		DECLARE @DEFAULT_WAFER_IDS_TABLE		TABLE ( WAFER_ID INT ) 
		DECLARE @WAFER_COUNT			INT
		DECLARE @WAFER_ID     			INT
		DECLARE @BIN_QTY				INT
		DECLARE @MODULUS_REMAIN_QTY		INT
		DECLARE @TEMP_COUNT				INT
		DECLARE @GROSS_DIE_QTY			INT
		DECLARE @NO_OF_WAFERS			INT
		DECLARE @DIE_QTY				INT
		DECLARE @FAB_WAFER_IDS			VARCHAR(200)
		DECLARE @SORT_WAFER_IDS			VARCHAR(200)
		DECLARE @BYNEXTLOCATION INT
		DECLARE @DIEBANK_VENDOR			VARCHAR(50)
		DECLARE @SO_NO INT

		SET @FAB_WAFER_IDS = NULL
		SET @SORT_WAFER_IDS = NULL
		
		SET @DEFAULT_WAFERS = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25'

		SELECT @DIEBANK_VENDOR = L_Vendor_Name FROM Location WHERE Location_ID = ( SELECT Location_ID FROM Lot_Table where Lot_ID = @DB_LOT_ID )

		INSERT INTO @DEFAULT_WAFER_IDS_TABLE (WAFER_ID) SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @DEFAULT_WAFERS )
				
		SELECT @GROSS_DIE_QTY = ISNULL(PARAMETER_VALUE,0) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = (SELECT TOP 1 PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID(@DB_PROCESSSTEP_ID,0) WHERE PS_TYPE = 'FAB' ) AND Parameter_KEY = 'Gross Die/Wafer'
		
		IF @DIEBANK_WAFER_IDS IS NOT NULL AND @LOT_FROM = 'adaptorLot'
		BEGIN
				SET @DIEBANK_WAFER_IDS = REPLACE( @DIEBANK_WAFER_IDS, ';', ',')
				INSERT INTO @WAFER_IDS_TABLE (WAFER_ID) SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @DIEBANK_WAFER_IDS )
		END
		ELSE
		BEGIN
			SELECT TOP 1 @SORT_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @DB_PROCESSSTEP_ID,0 ) WHERE PS_TYPE = 'SORT'
			
			IF @SORT_PROCESSSTEP_ID IS NOT NULL
			BEGIN
				SELECT TOP 1 @SORT_LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @SORT_PROCESSSTEP_ID AND FAB_LOT_NUMBER = @FAB_LOT_NUMBER ORDER BY TIMESTAMP
				
				IF @SORT_LOT_ID IS NOT NULL
				BEGIN		
					IF ( (SELECT COUNT(WAFER_ID) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @SORT_LOT_ID) = 0 )
					BEGIN
						PRINT 'IN IFFFFFFFFFFFF'
						-- This falg "0" means "Manual Binning"  helps to update the Binning information once the Sort Information is available
						UPDATE Lot_Table SET SHOP_ORDER_NUMBER = 0 WHERE Lot_ID = @DB_LOT_ID
						SET @BYNEXTLOCATION = 0
						SELECT @SORT_WAFER_IDS = WAFER_ID FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.LOT_ID = LT.LOT_ID AND LT.Lot_Id = @SORT_LOT_ID AND ISNULL( SL.SL_NextLocation, '') = @DIEBANK_VENDOR
						IF @SORT_WAFER_IDS IS NULL OR LEN(@SORT_WAFER_IDS) = 0
						BEGIN
							SET @BYNEXTLOCATION = 1
							SELECT @SORT_WAFER_IDS = WAFER_ID FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.LOT_ID = LT.LOT_ID AND LT.Lot_Id = @SORT_LOT_ID
						END

						IF @SORT_WAFER_IDS IS NOT NULL
						BEGIN
							SET @SHIPPED_WAFER_IDS = ''
							DECLARE @sort_ship_wafer_cursor CURSOR 
							IF @BYNEXTLOCATION = 0
							BEGIN
								SET @sort_ship_wafer_cursor = CURSOR FOR SELECT WAFER_ID FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.LOT_ID = LT.LOT_ID AND LT.Lot_Id = @SORT_LOT_ID AND SL.SL_NextLocation = @Diebank_Vendor
							END
							ELSE
							BEGIN
								SET @sort_ship_wafer_cursor = CURSOR FOR SELECT WAFER_ID FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.LOT_ID = LT.LOT_ID AND LT.Lot_Id = @SORT_LOT_ID
							END
							
							OPEN @sort_ship_wafer_cursor
							FETCH NEXT FROM @sort_ship_wafer_cursor INTO @SHIPPED_WAFER_ID
							WHILE @@FETCh_STATUS = 0
							BEGIN
								SET @SHIPPED_WAFER_IDS = @SHIPPED_WAFER_IDS + ';' + @SHIPPED_WAFER_ID				
								FETCH NEXT FROM @sort_ship_wafer_cursor INTO @SHIPPED_WAFER_ID
							END
							CLOSE @sort_ship_wafer_cursor 
							DEALLOCATE @sort_ship_wafer_cursor

							SET @ALL_SHIPPED_WAFER_IDS = REPLACE( @SHIPPED_WAFER_IDS, ';', ',')

							INSERT INTO @WAFER_IDS_TABLE (WAFER_ID) SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @ALL_SHIPPED_WAFER_IDS )
						END
						ELSE
						BEGIN
							INSERT INTO @WAFER_IDS_TABLE (WAFER_ID) SELECT WAFER_ID FROM @DEFAULT_WAFER_IDS_TABLE	
						END
					END --WAFER COUNT OF SORT BIN IS GREATER
					ELSE  
					BEGIN
						-- HERE WE NEED TO DUMP THIS INFO FOR DIEBANK LOT ID ?		
						--INSERT INTO @WAFER_IDS_TABLE SELECT COUNT(WAFER_ID) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @SORT_LOT_ID
						
						DECLARE @sort_bin_wafer_cursor CURSOR 
						SET @sort_bin_wafer_cursor = CURSOR FOR SELECT WAFER_ID, BIN_1_Balance_Qty FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @SORT_LOT_ID
						OPEN @sort_bin_wafer_cursor
						FETCH NEXT FROM @sort_bin_wafer_cursor INTO @WAFER_ID, @BIN_QTY
						WHILE @@FETCh_STATUS = 0
						BEGIN
							EXEC SP_SAVE_INFORMATIVE_BINNING_INFO @DB_LOT_ID, @WAFER_ID,'Bin1',@BIN_QTY,0/*rejectedqty*/, 0.0/*yield*/
							
							SET @WAFER_ID		= NULL
							SET @BIN_QTY		= NULL	
							FETCH NEXT FROM @sort_bin_wafer_cursor INTO @WAFER_ID, @BIN_QTY
						END
						CLOSE @sort_bin_wafer_cursor 
						DEALLOCATE @sort_bin_wafer_cursor
						--This falg "1" means "Wafer Binning"  
						UPDATE Lot_Table SET SHOP_ORDER_NUMBER = 1 WHERE Lot_ID = @DB_LOT_ID
					END
				END
				ELSE --WHEN THERE IS NO SORT LOT INSERTED
				BEGIN
					UPDATE Lot_Table SET SHOP_ORDER_NUMBER = 0 WHERE Lot_ID = @DB_LOT_ID
					INSERT INTO @WAFER_IDS_TABLE (WAFER_ID) SELECT WAFER_ID FROM @DEFAULT_WAFER_IDS_TABLE
				END
			END
			ELSE -- WHERE SORT PROCESS NOT FOUND
			BEGIN
				SELECT TOP 1 @FAB_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @DB_PROCESSSTEP_ID,0 ) WHERE PS_TYPE = 'FAB'
				IF @FAB_PROCESSSTEP_ID IS NOT NULL
				BEGIN
					SELECT @FAB_LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID AND FAB_LOT_NUMBER= @FAB_LOT_NUMBER
					
					IF @FAB_LOT_ID IS NOT NULL
					BEGIN
						--SELECT WAFER_ID FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.LOT_ID = LT.LOT_ID AND LT.Lot_Id = @FAB_LOT_ID
						SELECT @FAB_WAFER_IDS = WAFER_ID FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.LOT_ID = LT.LOT_ID AND LT.Lot_Id = @FAB_LOT_ID
						IF @FAB_WAFER_IDS IS NOT NULL
						BEGIN
							SET @SHIPPED_WAFER_IDS = ''
							DECLARE @fab_ship_wafer_cursor CURSOR 
							SET @fab_ship_wafer_cursor = CURSOR FOR SELECT WAFER_ID FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.LOT_ID = LT.LOT_ID AND LT.Lot_Id = @FAB_LOT_ID
							OPEN @fab_ship_wafer_cursor
							FETCH NEXT FROM @fab_ship_wafer_cursor INTO @SHIPPED_WAFER_ID
							WHILE @@FETCh_STATUS = 0
							BEGIN
								SET @SHIPPED_WAFER_IDS = @SHIPPED_WAFER_IDS + ';' + @SHIPPED_WAFER_ID				
								FETCH NEXT FROM @fab_ship_wafer_cursor INTO @SHIPPED_WAFER_ID
							END
							CLOSE @fab_ship_wafer_cursor 
							DEALLOCATE @fab_ship_wafer_cursor
							
							SET @ALL_SHIPPED_WAFER_IDS = REPLACE( @SHIPPED_WAFER_IDS, ';', ',')

							INSERT INTO @WAFER_IDS_TABLE (WAFER_ID) SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @ALL_SHIPPED_WAFER_IDS )	
						END
						ELSE
						BEGIN
							INSERT INTO @WAFER_IDS_TABLE (WAFER_ID) SELECT WAFER_ID FROM @DEFAULT_WAFER_IDS_TABLE	
						END

					END
					ELSE --WHEN THERE IS NO FAB LOT INSERTED
					BEGIN
						INSERT INTO @WAFER_IDS_TABLE (WAFER_ID) SELECT WAFER_ID FROM @DEFAULT_WAFER_IDS_TABLE
					END
				END
				ELSE --WHEN THERE IS NO FAB PROCESS STEP ID ( FOR BOM lOTS)
				BEGIN
					INSERT INTO @WAFER_IDS_TABLE (WAFER_ID) SELECT WAFER_ID FROM @DEFAULT_WAFER_IDS_TABLE
				END
			END--END OF ELSE WHEN NO SORT PROCESS
		END --END OF ELSE BLOCK, WHEN DIEBANK RECEIVE WAFERS NOT PRESENT
		
		--HERE BASED ON THE DIE BANK QTY, INSERTING INTO BIN TABLE WITH DIE BANK LOT ID 
		SELECT @WAFER_COUNT = COUNT(*) FROM @WAFER_IDS_TABLE							
		
		IF @WAFER_COUNT > 0
		BEGIN	
			
			IF @P_TYPE IS NOT NULL AND @P_TYPE = 'BOM'
			BEGIN
				SET @MODULUS_REMAIN_QTY = @QTY_IN%@WAFER_COUNT
				SET @TEMP_COUNT = 1

				WHILE @TEMP_COUNT <= @WAFER_COUNT
				BEGIN
					SELECT TOP 1 @WAFER_ID = WAFER_ID FROM @WAFER_IDS_TABLE
					IF @TEMP_COUNT <= @MODULUS_REMAIN_QTY
					BEGIN
							SET @DIE_QTY = (@QTY_IN/@WAFER_COUNT) + 1
					END	
					ELSE
					BEGIN
							SET @DIE_QTY = (@QTY_IN/@WAFER_COUNT)
					END

					EXEC SP_SAVE_INFORMATIVE_BINNING_INFO @DB_LOT_ID, @WAFER_ID,'Bin1',@DIE_QTY,0/*rejectedqty*/, 0.0/*yield*/

					SET @TEMP_COUNT = @TEMP_COUNT + 1
					DELETE FROM @WAFER_IDS_TABLE WHERE WAFER_ID = @WAFER_ID
					SET @WAFER_ID = NULL
					SET @DIE_QTY  = NULL
				END	
			
			END --@P_TYPE IS NOT NULL
			ELSE
			BEGIN
				IF @Gross_die_qty > 0
				BEGIN
					SET @NO_OF_WAFERS	= @QTY_IN/@Gross_die_qty
				
					IF @QTY_IN%@Gross_die_qty > 0
					BEGIN
						SET @NO_OF_WAFERS = @NO_OF_WAFERS + 1
						SET @MODULUS_REMAIN_QTY = @QTY_IN%@Gross_die_qty
					END
				END
				
				INSERT INTO @WAFER_IDS_TABLE SELECT WAFER_ID FROM @DEFAULT_WAFER_IDS_TABLE WHERE WAFER_ID NOT IN ( SELECT WAFER_ID FROM @WAFER_IDS_TABLE)
				SET @TEMP_COUNT = 1
				WHILE @TEMP_COUNT <= @NO_OF_WAFERS
				BEGIN		
						SELECT TOP 1 @WAFER_ID = WAFER_ID FROM @WAFER_IDS_TABLE						
						IF @TEMP_COUNT = @NO_OF_WAFERS AND @MODULUS_REMAIN_QTY > 0
						BEGIN
							EXEC SP_SAVE_INFORMATIVE_BINNING_INFO @DB_LOT_ID, @WAFER_ID,'Bin1',@MODULUS_REMAIN_QTY,0/*rejectedqty*/, 0.0/*yield*/
						END
						ELSE
						BEGIN
							EXEC SP_SAVE_INFORMATIVE_BINNING_INFO @DB_LOT_ID, @WAFER_ID,'Bin1',@GROSS_DIE_QTY,0/*rejectedqty*/, 0.0/*yield*/
						END
						SET @TEMP_COUNT = @TEMP_COUNT + 1
						DELETE FROM @WAFER_IDS_TABLE WHERE WAFER_ID = @WAFER_ID
						SET @WAFER_ID = NULL
				END
			END--END OF ELSE @P_TYPE IS NULL
		END								
	END
